/**
 * 
 */
package com.htht.smartschool.service.bigexam;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;

import com.htht.smartschool.common.service.BaseService;
import com.htht.smartschool.model.bigexam.AdminExamResultBean;
import com.htht.smartschool.model.bigexam.BigExamBean;
import com.htht.smartschool.model.bigexam.CourseBean;
import com.htht.smartschool.model.bigexam.RankingBean;
import com.htht.smartschool.model.bigexam.SmallTestBean;
import com.htht.smartschool.model.bigexam.StudentInfoBean;
import com.htht.smartschool.model.bigexam.SubjectInfoBean;
import com.htht.smartschool.model.models.Models;
import com.htht.smartschool.model.student.TStudent;
import com.htht.smartschool.util.system.ExportExcel;
import com.htht.smartschool.util.system.ReflectionUtils;
import com.htht.smartschool.util.system.ResultSetToExcel;

/**
 * @author wuqiwei
 *
 */
public class BigExamService extends BaseService{
	ExportExcel exportExcel = new ExportExcel();
	@SuppressWarnings("rawtypes")
	private Map maprank = new HashMap();//放排名的
	@SuppressWarnings("rawtypes")
	private Map mapscore = new HashMap();//放成绩的
	@SuppressWarnings("rawtypes")
	private Map mapclassrank = new HashMap();//单科的班级排名
	@SuppressWarnings({ "rawtypes" })
	private Map parentChildrenSchoolRank = new HashMap();//获取家长端孩子的单科成绩 学校排名
	@SuppressWarnings({ "unused", "rawtypes" })
	private Map parentChilderenTotalRank = new HashMap();//获取家长端孩子的总成绩 学校排名
	@SuppressWarnings({ "rawtypes", "unused" })
	private Map studentInfoMap = new HashMap();//放学生信息的map
	/**考试总榜信息
	 * @author:wuqiwei
	 * @param @param examcode
	 * @param @return
	 * @date:2016年6月22日上午11:22:32
	*/
	@SuppressWarnings("unchecked")
	public Map getExamTotalList(BigExamBean big) {
		Map map = new HashMap();
		getRankingInfo(big.getExamcode(),big.getSchoolcode());//获取班级排名
		String sql = " SELECT distinct t1.studentname,t1.examcode,t1.studentcode,t1.classesname,t1.coursename,t1.scorenumber,t1.classcode,t1.grade,t1.schoolcode,t3.schoolrank,t3.scorecount,tb.examname FROM "
				+ " (SELECT studentname, examcode, studentcode, classesname, coursename, scorenumber,classcode,grade,schoolcode FROM t_result WHERE examcode = :examcode ORDER BY studentname) t1, "
				+ " (SELECT t2.*, @rownum\\:=@rownum+1 schoolrank FROM (SELECT SUM(scorenumber) scorecount, examcode, studentcode, @rownum \\:= 0 FROM t_result WHERE examcode = :examcode GROUP BY examcode,studentcode ORDER BY scorecount DESC)t2) t3,(SELECT * FROM t_big_exam) tb"
				+ " where t1.examcode = t3.examcode  AND tb.examcode = t1.examcode and t1.studentcode = t3.studentcode and t1.schoolcode = :schoolcode";
		if(big.getGrades() != null && big.getGrades().length() > 0){
			sql += " and t1.grade = :grades";
		}
		if(big.getClasscode() != null && big.getClasscode().length() > 0){
			sql += " and t1.classcode = :classcode";
		}
			sql += " ORDER  BY schoolrank,t1.studentname,t1.coursename";
		List<Object[]> list = null;
		if(big.getCurrentpage() != null && big.getPagesize() != null){
			 list = getDao().queryBySqlPage(sql,big,big.getCurrentpage(),big.getPagesize()); 
			 String sql2 = "selesct count(*) from ("+ sql +") t";
			int counts = 0;
			counts = getDao().getCountBySqlPage(sql2,big);
			map.put("total", counts);
		}else {
			 list = getDao().queryBySQL(sql,big);
		}
		List<BigExamBean> result = new ArrayList<BigExamBean>();
		List<SubjectInfoBean> subject = new ArrayList<SubjectInfoBean>();
		int j = 0;
		for(int i = 0;i<list.size();i++){
			Object[] obj = list.get(i);
			BigExamBean exam = new BigExamBean();
			SubjectInfoBean sujkect = new SubjectInfoBean();
			String examcode = String.valueOf(obj[1]);
			String studentcode = String.valueOf(obj[2]);
			if(j == 0){
				exam.setStudentname(String.valueOf(obj[0]));
				exam.setStudentcode(String.valueOf(obj[2]));
				exam.setClassesname(String.valueOf(obj[3]));
				exam.setExamcode(String.valueOf(obj[1]));
				sujkect.setSubject(String.valueOf(obj[4]));
				sujkect.setScore(String.valueOf(obj[5]));
//				subject.add(sujkect);
				exam.setSubjectinfo(subject);
				exam.setScorenumber(String.valueOf(mapscore.get(studentcode)) );
				exam.setClassranking(String.valueOf(maprank.get(studentcode)));
				double schoolrank = Double.valueOf(String.valueOf(obj[9]));
				Long a = Math.round(schoolrank);
				exam.setSchoolrank(a);
				exam.setExamname(String.valueOf(obj[11]));
				result.add(exam);
				j++;
			}else{
				if(examcode.equalsIgnoreCase(result.get(j-1).getExamcode()) && studentcode.equalsIgnoreCase(result.get(j-1).getStudentcode())){//看返回的数据格式 跟第一次做比较 相同的数据就不添加 只添加课程和成绩
					sujkect.setSubject(String.valueOf(obj[4]));
					sujkect.setScore(String.valueOf(obj[5]));
//					subject.add(sujkect);
					exam.setSubjectinfo(subject);
				}else{
					subject = new ArrayList<SubjectInfoBean>();
					exam.setStudentname(String.valueOf(obj[0]));
					exam.setStudentcode(String.valueOf(obj[2]));
					exam.setExamcode(String.valueOf(obj[1]));
					exam.setClassesname(String.valueOf(obj[3]));
					sujkect.setSubject(String.valueOf(obj[4]));
					sujkect.setScore(String.valueOf(obj[5]));
//					subject.add(sujkect);
					exam.setSubjectinfo(subject);
					exam.setScorenumber(String.valueOf(mapscore.get(studentcode)) );
					exam.setClassranking(String.valueOf(maprank.get(studentcode)));
					double schoolrank = Double.valueOf(String.valueOf(obj[9]));
					Long a = Math.round(schoolrank);
					exam.setSchoolrank(a);
					exam.setExamname(String.valueOf(obj[11]));
					result.add(exam);
					j++;
				}
			}
			subject.add(sujkect);
		}
//		// 导出考试总榜成绩excel
//		ResultSetToExcel rse = null;
//		if (big.getIsdownload() != null
//				&& big.getIsdownload().equalsIgnoreCase("true")) {
//			rse = new ResultSetToExcel();
//			rse.setColoumItem("schoolid,name,sex,birdate,birplacecode,birplace,nation,country,cardtype,cardid,oldname,cardvalid,isoutland,health,bloodtype,political,hukounature,hukouplace,address,mailaddress,familyaddress,familyphone,post,classno,inschooldate,Inschooltype,studytype,schoolno,classstudentid,studentfrom,specialty,email,homepage,isonechild,isbeforestudy,isstay,issupport,isonesupport,isorphan,isallow,istransfer,disabilitytype,isbuy,distance,waytype,isbus,studyclass,fname1,frelation1,fremark1,faddress1,fhukouplacecode1,fphone1,fisguardian1,fcardtype1,fcardid1,fnation1,fcom1,fjob1,Fname2,Frelation2,Fremark2,Faddress2,Fhukouplacecode2,Fphone2,Fisguardian2,Fcardtype2,Fcardid2,Fnation2,Fcom2,Fjob2,classcode,studentcode,buscode,del,headimg,Phone,messageid,cardcode,cellphone,imei,invisiblecode,model,remark,ring,ringswf,sostype,workhourcode,clazz,grade");
//			rse.setExam(result);
//			rse.writeExcel();
//		}
		map.put("data", result);
		return map;
	}

	/**获取班级排名
	 * @author:wuqiwei
	 * @param @return
	 * @date:2016年6月22日下午2:37:11
	*/
	@SuppressWarnings("unchecked")
	private void getRankingInfo(String examcode,String schoolcode) {
		String sql  = " SELECT classes,studentcode,examcode,score,rank "
				+ " FROM ( SELECT classes,studentcode,examcode,score,@rownum\\:=@rownum+1 , IF(@classes=classes,@rank\\:=@rank+1,@rank\\:=1) AS rank, @classes\\:=classes "
				+ " FROM ( SELECT classes,studentcode,examcode,SUM(scorenumber) score FROM t_result WHERE examcode = '"+examcode+"' AND schoolcode = '"+schoolcode+"' GROUP BY studentcode,examcode ORDER BY classes ASC,score DESC ) result_tmp ,"
				+ " (SELECT @rownum \\:=0 , @classes \\:= NULL ,@rank\\:=0) tmp ) result";
		List list = getDao().queryBySQL(sql);
		for(int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			double rank = Double.valueOf(String.valueOf(obj[4]));
			double scorenum = Double.valueOf(String.valueOf(obj[3]));
			maprank.put(String.valueOf(obj[1]), Math.round(rank));
			mapscore.put(String.valueOf(obj[1]),  Math.round(scorenum));
		}
	}
	/**
	 * 编辑考试 添加新的数据
	 * @author:wuqiwei
	 * @param @param biglist
	 * @param @return
	 * @date:2016年6月24日下午3:37:17
	 */
	public boolean addExamInfo(List<BigExamBean> biglist) {
		boolean flag = false;
		Date now = new Date();
		long examcode = now.getTime()/1000;
		String code = biglist.get(0).getExamcode();
		String sql = " insert into t_big_exam(examcode,examname,examtype,createtime,grade,type,schoolcode,describes,remark,examtime) values('"+biglist.get(0).getExamcode()+"','"+biglist.get(0).getExamname()+"','"+biglist.get(0).getExamtype()+"','"+examcode+"','"+biglist.get(0).getGrades()+"','"+biglist.get(0).getSchooltype()+"','"+biglist.get(0).getSchoolcode()+"','"+biglist.get(0).getDescribe()+"','"+biglist.get(0).getRemark()+"','"+biglist.get(0).getStime()+"')";
		int a = getDao().executeSQL(sql);
		if( a >= 0){
			String[] classcode = biglist.get(0).getClasscode().split(",");
			for(int i = 0;i<classcode.length;i++){
				String examclassessql = " insert into t_big_exam_classes(examcode,classcode,grade,type,remark,schoolcode) values('"+biglist.get(0).getExamcode()+"','"+classcode[i]+"','"+biglist.get(0).getGrades()+"','"+biglist.get(0).getSchooltype()+"','"+biglist.get(0).getRemark()+"','"+biglist.get(0).getSchoolcode()+"')";
				int b = getDao().executeSQL(examclassessql);
			}
		}
		for(int j = 0;j<biglist.size();j++){
			String examcoursesql = " insert into t_big_exam_course(examcode,coursecode,coursename,stime,etime,score,remark) values('"+biglist.get(0).getExamcode()+"','"+biglist.get(j).getCoursecode()+"','"+biglist.get(j).getCoursename()+"','"+biglist.get(j).getStime()+"','"+biglist.get(j).getEtime()+"','"+biglist.get(j).getScore()+"','"+biglist.get(j).getRemark()+"')";
			int c = getDao().executeSQL(examcoursesql);
//			if(c >= 0){
//				flag = true;
//			}
		}
		boolean result = addResult(biglist,code);//批量添加学生
		if(result){
			flag = true;
		}
		return flag;
	}
	/**添加考试
	 * @author:wuqiwei
	 * @param @param big
	 * @param @return
	 * @date:2016年6月22日下午4:47:41
	*/
	@SuppressWarnings("unchecked")
	public Map addBigExamInfo(List<BigExamBean> biglist) {
		Map map = new HashMap();
		boolean flag = false;
		Date now = new Date();
		long examcode = now.getTime()/1000;
		String sql = " insert into t_big_exam(examcode,examname,examtype,createtime,grade,type,schoolcode,describes,remark) values('"+examcode+"','"+biglist.get(0).getExamname()+"','"+biglist.get(0).getExamtype()+"','"+examcode+"','"+biglist.get(0).getGrades()+"','"+biglist.get(0).getSchooltype()+"','"+biglist.get(0).getSchoolcode()+"','"+biglist.get(0).getDescribe()+"','"+biglist.get(0).getRemark()+"')";
		int a = getDao().executeSQL(sql);
		if( a >= 0){
			String[] classcode = biglist.get(0).getClasscode().split(",");
			for(int i = 0;i<classcode.length;i++){
				String examclassessql = " insert into t_big_exam_classes(examcode,classcode,grade,type,remark,schoolcode) values('"+examcode+"','"+classcode[i]+"','"+biglist.get(0).getGrades()+"','"+biglist.get(0).getSchooltype()+"','"+biglist.get(0).getRemark()+"','"+biglist.get(0).getSchoolcode()+"')";
				int b = getDao().executeSQL(examclassessql);
			}
		}
		for(int j = 0;j<biglist.size();j++){
			String examcoursesql = " insert into t_big_exam_course(examcode,coursecode,coursename,stime,etime,score,remark) values('"+examcode+"','"+biglist.get(j).getCoursecode()+"','"+biglist.get(j).getCoursename()+"','"+biglist.get(j).getStime()+"','"+biglist.get(j).getEtime()+"','"+biglist.get(j).getScore()+"','"+biglist.get(j).getRemark()+"')";
			int c = getDao().executeSQL(examcoursesql);
			if(c >= 0){
				flag = true;
			}
		}
		String code = String.valueOf(examcode);
		map.put("flag", flag);
		map.put("examcode", code);
		return map;
	}
	
	public boolean addResult(List<BigExamBean> biglist,String examcode){
		boolean flag = false;
		int m = 0;
		List queries = new ArrayList();
		for(int i=0;i<biglist.size();i++){
			BigExamBean beb = biglist.get(i);
			String classcode = beb.getClasscode();
			String[] classcodeArr = classcode.split(",");
			flag = addStudentResult(classcodeArr,beb,m,queries,examcode);
		}
		getDao().addBatchs(queries);
		return flag;
	}

	public boolean addStudentResult(String[] classcodeArr,BigExamBean beb,int m ,List queries,String examcode){
		int n = 0;
		boolean flag = false;
		for(int i=0;i<classcodeArr.length;i++){
			List<Object[]> studentList = studentList(classcodeArr[i]);
//			queries = new String[studentList.size()];
			for(int j=0;j<studentList.size();j++){
				Object[] obj = studentList.get(j);
				String sql = "insert into t_result (examcode,coursecode,coursename,studentcode,studentname,classesname,grade,classes,classcode,scorenumber,schoolcode,remark,type) "
						+ " values('"+examcode+"','"+beb.getCoursecode()+"','"+beb.getCoursename()+"','"+obj[0]+"','"+obj[1]+"','"+beb.getGrades()+"年级"+obj[3]+"班',"+beb.getGrades()+","+obj[3]+",'"+classcodeArr[i]+"',0,'"+beb.getSchoolcode()+"','',"+beb.getSchooltype()+")";
				n = getDao().executeSQL(sql);
//				queries.add(sql);
				if(n > 0){
					flag = true;
				}
			}
		}
		return flag;
	}
	
	public List<Object[]> studentList(String classcodeArr){
		String sqlStudent = "select studentcode,name,grade,class FROM t_student ts,t_class tc WHERE ts.classcode = tc.classcode  and tc.classcode = '"+classcodeArr+"' ";
		return getDao().queryBySQL(sqlStudent);
	} 
	
	/**获取年级全部考试信息
	 * @author:wuqiwei
	 * @param @param big
	 * @param @return
	 * @date:2016年6月23日上午9:13:56
	*/
	@SuppressWarnings("unchecked")
	public Map getAllExamInfo(BigExamBean big) {
		Map map = new HashMap();
		big.setRolecode(big.getRolecode().replace(",", "^"));
		//查询这个角色 一共绑定多少个年级
		String sql = "SELECT GROUP_CONCAT(t1.grade) FROM (SELECT * FROM t_data_role WHERE rolecode IN (:rolecode) GROUP BY grade) t1 GROUP BY t1.rolecode";
		List list = getDao().queryBySQL(sql, big);
		List<BigExamBean> result = new ArrayList<BigExamBean>();
		BigExamBean exam = new BigExamBean();
		exam.setGrades(String.valueOf(list.get(0)));
		result.add(exam);
		//将年级放到一个字段里 用,号隔开
		String grade = result.get(0).getGrades();
		String exaxsql =  " SELECT t1.* FROM (SELECT * FROM t_big_exam WHERE schoolcode = '"+big.getSchoolcode()+"' AND grade IN("+grade+")) t1,(SELECT * FROM t_big_exam_classes)t2 WHERE t1.examcode = t2.examcode ";
		if(big.getClasscode() != null && big.getClasscode().length() > 0){
			exaxsql += " AND t2.classcode = '"+big.getClasscode()+"'";
		}
		if(big.getStarttime() != null && big.getStarttime() > 0){
			exaxsql += " AND t1.createtime >= '"+big.getStarttime()+"' ";
		}
		if(big.getEndtime() != null && big.getEndtime() > 0){
			exaxsql += " and t1.createtime <= '"+big.getEndtime()+"'";
		}
			exaxsql += " GROUP BY t1.id ORDER BY t1.createtime desc";
		List<Object[]> examlist = getDao().queryBySqlPage(exaxsql,big.getCurrentpage(), big.getPagesize());
		List<BigExamBean> examresult = new ArrayList<BigExamBean>();
		String sql2 = "select count(*) from ("+ exaxsql +") t";
		int count = 0;
		count = getDao().getCountBySqlPage(sql2,big);
		for(int j = 0;j<examlist.size();j++){
			Object[] obj = examlist.get(j);
			BigExamBean bigexam = new BigExamBean();
			bigexam.setId(Long.valueOf(String.valueOf(obj[0])));
			bigexam.setExamcode(String.valueOf(obj[1]));
			bigexam.setExamname(String.valueOf(obj[2]));
			bigexam.setCreatetime(Long.valueOf(String.valueOf(obj[4])));
			bigexam.setGrades(String.valueOf(obj[5]));
			bigexam.setSchooltype(String.valueOf(obj[6]));
			bigexam.setSchoolcode(String.valueOf(obj[7]));
			bigexam.setDescribe(obj[8] != null ? String.valueOf(obj[8]) : "");
			bigexam.setRemark(obj[9] != null ? String.valueOf(obj[9]) : "");
			examresult.add(bigexam);
		}
		map.put("data",examresult);
		map.put("total",count);
		return map;
	}

	/**获取考试详情
	 * @author:wuqiwei
	 * @param @param big
	 * @param @return
	 * @date:2016年6月23日上午10:46:11
	*/
	public List<BigExamBean> getExamDetial(BigExamBean big) {
		String sql = "  SELECT * FROM t_big_exam WHERE examcode = :examcode";
		List  list = getDao().queryBySQL(sql, big);
		List<BigExamBean> result = new ArrayList<BigExamBean>();
		Object[] obj = (Object[]) list.get(0);
		BigExamBean bean = new BigExamBean();
		bean.setExamcode(String.valueOf(obj[1]));
		bean.setExamname(String.valueOf(obj[2]));
		bean.setCreatetime(Long.valueOf(String.valueOf(obj[4])));
		bean.setGrades(String.valueOf(obj[5]));
		bean.setSchooltype(String.valueOf(obj[6]));
		bean.setSchoolcode(String.valueOf(obj[7]));
		bean.setDescribe(obj[8] != null ? String.valueOf(obj[8]) : "");
		bean.setRemark(obj[9] != null ? String.valueOf(obj[9]) : "");
		if(list.size()>0){
			String  qclasssql = " SELECT t1.grade,GROUP_CONCAT(t2.classcode) FROM (SELECT * FROM t_big_exam_classes ) t1,(SELECT * FROM t_class) t2 WHERE t1.classcode = t2.classcode AND t1.examcode = :examcode and t1.schoolcode = :schoolcode GROUP BY t1.grade ";
			List  classresult= getDao().queryBySQL(qclasssql, big);
			Object[] obje = (Object[]) classresult.get(0);
			bean.setClasscode(String.valueOf(obje[1]));
			String coursql = " SELECT * FROM t_big_exam_course WHERE examcode = :examcode";
			List courlist = getDao().queryBySQL(coursql, big);
			List<CourseBean> courseresult =  new ArrayList<CourseBean>();
			for(int i = 0;i<courlist.size();i++){
				Object[] co = (Object[]) courlist.get(i);
				CourseBean cour = new CourseBean();
				cour.setCoursename(String.valueOf(co[3]));
				cour.setCoursecode(String.valueOf(co[2]));
				cour.setStarttime(Long.valueOf(String.valueOf(co[4])));
				cour.setEndtime(Long.valueOf(String.valueOf(co[5])));
				cour.setScore(String.valueOf(co[6]));
				courseresult.add(cour);
			}
			bean.setCourselist(courseresult);
			result.add(bean);
		}
		return result;
	}

	/**编辑考试信息
	 * @author:wuqiwei
	 * @param @param big
	 * @param @return
	 * @date:2016年6月23日下午3:17:27
	*/
	public boolean UpdateExamInfo(List<BigExamBean> biglist) {
		boolean flag = false;
		//修改的话 是将这次考试的数据全部删除 然后在添加
		String sql = " delete from t_big_exam where examcode = '"+biglist.get(0).getExamcode()+"' and schoolcode = '"+biglist.get(0).getSchoolcode()+"' ";
		int a = getDao().executeSQL(sql);
		if( a > 0){
			String clsql = " delete from t_big_exam_classes where examcode = '"+biglist.get(0).getExamcode()+"' and schoolcode = '"+biglist.get(0).getSchoolcode()+"'";
			int b = getDao().executeSQL(clsql);
			if( b > 0){
				String coursql = " delete from t_big_exam_course where examcode = '"+biglist.get(0).getExamcode()+"'";
				int c = getDao().executeSQL(coursql);
				if( c > 0){
					String drsql = " delete from t_result where examcode = '"+biglist.get(0).getExamcode()+"'";
					int d = getDao().executeSQL(drsql);
					if( d > 0){
						boolean result = addExamInfo(biglist);
						if(result){
							flag = true;
						}
					}
				}
			}
		}
		return flag;
	}

	/**删除考试
	 * @author:wuqiwei
	 * @param @param big
	 * @param @return
	 * @date:2016年6月23日下午3:39:16
	*/
	public boolean deleteExamInfo(BigExamBean big) {
		boolean flag = false;
		String sql  = " delete from t_big_exam where examcode = :examcode and schoolcode = :schoolcode";
		int a = getDao().executeSQL(sql,big);
		if( a > 0){
			String clsql = " delete from t_big_exam_classes where examcode =:examcode and schoolcode = :schoolcode";
			int b = getDao().executeSQL(clsql,big);
			if( b > 0){
				String coursql = " delete from t_big_exam_course where examcode = :examcode and coursecode = :couesecode";
				int c = getDao().executeSQL(coursql,big);
				if( c > 0){
					flag = true;
				}
			}
		}
		return flag;
	}

	/**查看每次考试的详细信息
	 * @author:wuqiwei
	 * @param @param big
	 * @param @return
	 * @date:2016年6月23日下午3:50:04
	*/
	@SuppressWarnings({ "rawtypes", "unused" })
	public List<BigExamBean> getOneExamInfo(BigExamBean big) {
		String sql = " SELECT * FROM t_big_exam_course WHERE examcode = :examcode";
		List list = getDao().queryBySQL(sql, big);
		List<BigExamBean> result = new ArrayList<BigExamBean>();
		for(int i = 0;i<list.size();i++){
			Object[] obj =  (Object[]) list.get(i);
			BigExamBean bean = new BigExamBean();
			bean.setId(Long.valueOf(String.valueOf(obj[0])));
			bean.setExamcode(String.valueOf(obj[1]));
			bean.setCoursecode(String.valueOf(obj[2]));
			bean.setCoursename(String.valueOf(obj[3]));
			bean.setStime(Long.valueOf(String.valueOf(obj[4])));
			bean.setEtime(Long.valueOf(String.valueOf(obj[5])));
			bean.setScore(String.valueOf(obj[6]));
			bean.setRemark(obj[7] != null ? String.valueOf(obj[7]) : "");
			result.add(bean);
		}
		return result;
	}

	/**查看每科考试学生成绩的信息
	 * @author:wuqiwei
	 * @param @param big
	 * @param @return
	 * @date:2016年6月24日上午9:59:01
	*/
	@SuppressWarnings("unchecked")
	public Map getEveryExamInfo(BigExamBean big) {
		Map map = new HashMap();
		getEveryExamSchoolRank(big);//获取单科成绩的班级排名
		//家长端查看的时候 只会有一个孩子传入进来
//		big.setStudentcode(big.getStudentcode().replace(",", "^"));
		String sql = " SELECT t.*,@rownum \\:= @rownum+1 FROM (SELECT *,@rownum \\:= 0 FROM t_result WHERE coursecode = :coursecode AND examcode = :examcode and schoolcode = :schoolcode and type = :schooltype";
		if(big.getClasscode() != null && big.getClasscode().length() > 0){
			sql += " and classcode = :classcode";
		}
//		if(big.getStudentcode() != null && big.getStudentcode().length() > 0){
//			sql += " and studentcode = :studentcode";
//		}
			sql += " ORDER BY scorenumber*1 DESC) t";
		List list = null;
		if(big.getCurrentpage() != null && big.getPagesize() != null){
			 list = getDao().queryBySqlPage(sql,big,big.getCurrentpage(),big.getPagesize()); 
		}else {
			 list = getDao().queryBySQL(sql,big);
		}
		String sql2 = "select count(*) from ("+ sql +") t";
		int count = 0;
		count = getDao().getCountBySqlPage(sql2,big);
		List<BigExamBean> result = new ArrayList<BigExamBean>();
		for(int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			BigExamBean bean = new BigExamBean();
			bean.setId(Long.valueOf(String.valueOf(obj[0])));
			bean.setExamcode(String.valueOf(obj[1]));
			bean.setCoursecode(String.valueOf(obj[2]));
			bean.setCoursename(String.valueOf(obj[3]));
			bean.setStudentcode(String.valueOf(obj[4]));
			bean.setStudentname(String.valueOf(obj[5]));
			bean.setClassesname(String.valueOf(obj[6]));
			bean.setGrades(String.valueOf(obj[7]));
			bean.setClasses(String.valueOf(obj[8]));
			bean.setClasscode(String.valueOf(obj[9]));
			bean.setScorenumber(String.valueOf(obj[10]));
			bean.setSchoolcode(String.valueOf(obj[11]));
			double schoolrank = Double.valueOf(String.valueOf(obj[15]));
			Long schrank =  Math.round(schoolrank);
			bean.setSchoolrank(schrank);
			bean.setClassranking(String.valueOf(mapclassrank.get(String.valueOf(obj[4]))));
			result.add(bean);
		}
		map.put("data", result);
		map.put("total", count);
		return map;
	}

	/**获取单科成绩的班级排名
	 * @author:wuqiwei
	 * @param @param big
	 * @date:2016年6月24日下午1:21:36
	*/
	@SuppressWarnings({ "unchecked", "rawtypes" })
	private void getEveryExamSchoolRank(BigExamBean big) {
		String sql = "SELECT examcode,coursecode,coursename,studentname,studentcode,classesname,grade,classes,classcode,scorenumber,schoolcode,rank "
				+ " FROM(SELECT examcode,coursecode,coursename,studentname,studentcode,classesname,grade,classes,classcode,scorenumber,schoolcode,@rownum\\:=@rownum+1,IF(@classcode=classcode,@rank\\:=@rank+1,@rank\\:=1) AS rank, @classcode\\:=classcode "
				+ " FROM (SELECT examcode,coursecode,coursename,studentname,studentcode,classesname,grade,classes,classcode,scorenumber,schoolcode "
				+ " FROM t_result WHERE examcode = :examcode AND coursecode = :coursecode AND grade = :grades and schoolcode =:schoolcode and type = :schooltype ";
				if(big.getClasscode() != null && big.getClasscode().length() > 0){
					sql += " and classcode = :classcode";
				}
				sql += " ORDER BY classes ASC, scorenumber*1 DESC) result_tmp, (SELECT @rownum \\:=0 , @classcode \\:= NULL ,@rank\\:=0) tmp ) result";
		List list = getDao().queryBySQL(sql, big);
		for(int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			double classrank = Double.valueOf(String.valueOf(obj[11]));
			mapclassrank.put(String.valueOf(obj[4]),Math.round(classrank));
		}
	}

	/**删除考试科目信息
	 * @author:wuqiwei
	 * @param @param big
	 * @param @return
	 * @date:2016年6月24日下午2:35:24
	*/
	public boolean deleteSubjectExamInfo(BigExamBean big) {
		boolean flag = false;
		String sql = " delete from t_big_exam_course where examcode = :examcode and coursecode = :coursecode";
		int a = getDao().executeSQL(sql, big);
		if( a > 0){
			String dsql = " delete from t_result where examcode = :examcode and coursecode = :coursecode";
			int b = getDao().executeSQL(dsql, big);
			if(b > 0){
				flag = true;
			}
		}
		return flag;
	}

	/**获取家长端孩子考试列表
	 * @author:wuqiwei
	 * @param @param big
	 * @param @return
	 * @date:2016年6月24日下午3:16:43
	*/
	public List<BigExamBean> getParentChildrenExamList(BigExamBean big) {
		big.setStudentcode(big.getStudentcode().replace(",", "^"));
		String sql = " SELECT t2.* FROM (SELECT * FROM t_result WHERE studentcode IN(:studentcode)) t1,(SELECT * FROM t_big_exam ) t2 WHERE t1.examcode = t2.examcode and t1.schoolcode = :schoolcode ";
		if(big.getStime() != null){
			sql += " and t2.examtime >= :stime";
		}
		if(big.getEtime() != null){
			sql += " and t2.examtime <= :etime";
		}
			sql += "GROUP BY t2.id ";
		List list = getDao().queryBySQL(sql, big);
		List<BigExamBean> result = new ArrayList<BigExamBean>();
		for( int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			BigExamBean bean = new BigExamBean();
			bean.setId(Long.valueOf(String.valueOf(obj[0])));
			bean.setExamcode(String.valueOf(obj[1]));
			bean.setExamname(String.valueOf(obj[2]));
			bean.setCreatetime(Long.valueOf(String.valueOf(obj[4])));
			bean.setGrades(String.valueOf(obj[5]));
			bean.setSchooltype(String.valueOf(obj[6]));
			bean.setSchoolcode(String.valueOf(obj[7]));
			bean.setDescribe(obj[8] != null ? String.valueOf(obj[8]) : "");
			result.add(bean);
		}
		return result;
	}

	/**获取家长端一科成绩成绩单详情
	 * @author:wuqiwei
	 * @param @param big
	 * @param @return
	 * @date:2016年6月25日上午11:28:23
	*/
	@SuppressWarnings("rawtypes")
	public List<BigExamBean> getParentChildrenOneEXamResult(BigExamBean big) {
		queryParentChildrenOneSubjectSchoolRank(big);//查询家长端一个孩子一科考试的学校排名
		getEveryExamSchoolRank(big);//获得孩子单科的班级排名
		String sql = " SELECT * FROM t_result WHERE studentcode = :studentcode AND examcode = :examcode AND schoolcode = :schoolcode AND coursecode = :coursecode";
		List list = getDao().queryBySQL(sql, big);
		List<BigExamBean> result = new ArrayList<BigExamBean>();
		for(int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			BigExamBean bean = new BigExamBean();
			bean.setExamcode(String.valueOf(obj[1]));
			bean.setCoursecode(String.valueOf(obj[2]));
			bean.setCoursename(String.valueOf(obj[3]));
			bean.setStudentcode(String.valueOf(obj[4]));
			bean.setStudentname(String.valueOf(obj[5]));
			bean.setClassesname(String.valueOf(obj[6]));
			bean.setGrades(String.valueOf(obj[7]));
			bean.setClasses(String.valueOf(obj[8]));
			bean.setClasscode(String.valueOf(obj[9]));
			bean.setScorenumber(String.valueOf(obj[10]));
			bean.setSchoolcode(String.valueOf(obj[11]));
			bean.setSchooltype(String.valueOf(obj[13]));
			Long schoolrank = (Long) parentChildrenSchoolRank.get(String.valueOf(obj[4]));
			bean.setSchoolrank(schoolrank);//校排名
			bean.setClassranking(String.valueOf(mapclassrank.get(String.valueOf(obj[4]))));//班级排名
			result.add(bean);
		}
		return result;
	}
	/**
	 * 查询家长端一个孩子一科考试的学校排名
	 * @author:wuqiwei
	 * @param 
	 * @date:2016年6月25日上午11:33:40
	 */
	@SuppressWarnings("unchecked")
	public void queryParentChildrenOneSubjectSchoolRank(BigExamBean big){
		String sql = " SELECT t.*,@rownum \\:= @rownum+1 FROM (SELECT *,@rownum \\:= 0 FROM t_result WHERE coursecode = :coursecode AND examcode = :examcode AND schoolcode = :schoolcode   ORDER BY scorenumber*1 DESC) t";
		List list = getDao().queryBySQL(sql, big);
		for(int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			double schoolrank = Double.valueOf(String.valueOf(obj[15]));
			parentChildrenSchoolRank.put(String.valueOf(obj[4]), Math.round(schoolrank));
		}

	}

	/**获取家长端一次考试孩子的成绩单
	 * @author:wuqiwei
	 * @param @param big
	 * @param @return
	 * @date:2016年6月25日下午3:45:16
	*/
	public List<BigExamBean> getParentChildrenOneExamRank(BigExamBean big) {
		getRankingInfo(big.getExamcode(),big.getSchoolcode());//获取成绩总分
		getParnentChildrenOneExamSchoolRank(big);//获取家长端孩子的成绩 学校排名
		String sql = " SELECT * FROM t_result  WHERE examcode = :examcode AND studentcode = :studentcode AND schoolcode = :schoolcode AND classcode = :classcode";
		List list = getDao().queryBySQL(sql, big);
		List<BigExamBean> result = new ArrayList<BigExamBean>();
		List<SubjectInfoBean> subject = new ArrayList<SubjectInfoBean>();
		int j = 0;
		for(int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			BigExamBean exam = new BigExamBean();
			SubjectInfoBean sujkect = new SubjectInfoBean();
			String examcode = String.valueOf(obj[1]);
			String studentcode = String.valueOf(obj[4]);
			if(j == 0){
				exam.setStudentname(String.valueOf(obj[5]));
				exam.setStudentcode(String.valueOf(obj[4]));
				exam.setClassesname(String.valueOf(obj[6]));
				exam.setExamcode(String.valueOf(obj[1]));
				sujkect.setSubject(String.valueOf(obj[3]));
				sujkect.setScore(String.valueOf(obj[10]));
				exam.setSubjectinfo(subject);
				exam.setScorenumber(String.valueOf(mapscore.get(studentcode)) );
				Long schoolrank = (Long) parentChilderenTotalRank.get(studentcode);
				exam.setSchoolrank(schoolrank);
				result.add(exam);
				j++;
			}else{
				if(examcode.equalsIgnoreCase(result.get(j-1).getExamcode()) && studentcode.equalsIgnoreCase(result.get(j-1).getStudentcode())){//看返回的数据格式 跟第一次做比较 相同的数据就不添加 只添加课程和成绩
					sujkect.setSubject(String.valueOf(obj[3]));
					sujkect.setScore(String.valueOf(obj[10]));
					exam.setSubjectinfo(subject);
				}else{
					subject = new ArrayList<SubjectInfoBean>();
					exam.setStudentname(String.valueOf(obj[5]));
					exam.setStudentcode(String.valueOf(obj[4]));
					exam.setExamcode(String.valueOf(obj[1]));
					exam.setClassesname(String.valueOf(obj[6]));
					sujkect.setSubject(String.valueOf(obj[3]));
					sujkect.setScore(String.valueOf(obj[10]));
					exam.setSubjectinfo(subject);
					exam.setScorenumber(String.valueOf(mapscore.get(studentcode)) );
					Long schoolrank = (Long) parentChilderenTotalRank.get(studentcode);
					exam.setSchoolrank(schoolrank);
					result.add(exam);
					j++;
				}
			}
			subject.add(sujkect);
		
		}
		return result;
	}

	/**获取家长端孩子的成绩 学校排名
	 * @author:wuqiwei
	 * @param @param big
	 * @date:2016年6月25日下午3:48:55
	*/
	@SuppressWarnings("unchecked")
	private void getParnentChildrenOneExamSchoolRank(BigExamBean big) {
		String sql = " SELECT t1.*,t3.schoolrank,t3.scorecount FROM  (SELECT studentname, examcode, studentcode, classesname, coursename, scorenumber,classcode,grade,schoolcode FROM t_result WHERE examcode = :examcode ORDER BY studentname) t1,  (SELECT t2.*, @rownum\\:=@rownum+1 schoolrank FROM (SELECT SUM(scorenumber) scorecount, examcode, studentcode, @rownum \\:= 0 FROM t_result WHERE examcode = :examcode GROUP BY examcode,studentcode ORDER BY scorecount DESC)t2) t3  WHERE t1.examcode = t3.examcode AND t1.studentcode = t3.studentcode AND t1.schoolcode = :schoolcode AND t1.grade = :grades";
		List list = getDao().queryBySQL(sql, big);
		List<BigExamBean> result = new ArrayList<BigExamBean>();
		List<SubjectInfoBean> subject = new ArrayList<SubjectInfoBean>();
		for(int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			double schoolrank = Double.valueOf(String.valueOf(obj[9]));
			parentChilderenTotalRank.put(String.valueOf(obj[2]), Math.round(schoolrank));
		}
	}

	/**获取教师端考试列表
	 * @author:wuqiwei
	 * @param @param big
	 * @param @return
	 * @date:2016年6月27日下午2:31:41
	*/
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Map getTeahcerAllExamInfo(BigExamBean big) {
		String sql = " SELECT tb.examcode, tb.examname, tb.createtime, tb.grade, tb.type, tb.schoolcode, tb.describes, tb.remark FROM (SELECT * FROM t_big_exam) tb, (SELECT * FROM t_big_exam_classes) tbc WHERE tb.examcode = tbc.examcode AND tbc.classcode = :classcode  AND tb.schoolcode = :schoolcode  AND tb.type = :schooltype";
		if(big.getStime() != null ){
			sql += " and tb.createtime >= :stime";
		}
		if(big.getEtime() != null){
			sql += " and tb.createtime <= :etime";
		}
			sql += " GROUP BY examcode";
		List list = getDao().queryBySqlPage(sql, big, big.getCurrentpage(), big.getPagesize());
		String sql2 = "select count(*) from ("+ sql +") t";
		int count = 0;
		count = getDao().getCountBySqlPage(sql2,big);
		List<BigExamBean> result = new ArrayList<BigExamBean>();
		for(int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			BigExamBean bean = new BigExamBean();
			bean.setExamcode(String.valueOf(obj[0]));
			bean.setExamname(String.valueOf(obj[1]));
			bean.setCreatetime(Long.valueOf(String.valueOf(obj[2])));
			bean.setGrades(String.valueOf(obj[3]));
			bean.setSchooltype(String.valueOf(obj[4]));
			bean.setSchoolcode(String.valueOf(obj[5]));
			bean.setDescribe(obj[6] != null ? String.valueOf(obj[6]) :"");
			bean.setRemark(obj[7] != null ? String.valueOf(obj[7]) :"");
			result.add(bean);
		}
		Map map = new HashMap();
		map.put("data", result);
		map.put("total", count);
		return map;
	}


	/**查询教师端学生信息
	 * @author:wuqiwei
	 * @param @param big
	 * @param @return
	 * @date:2016年6月27日下午3:25:38
	*/
	@SuppressWarnings("unchecked")
	public Map getTeahcerAllStudent_bak(BigExamBean big) {
		Map map = new HashMap();
		String sql = "SELECT ts.name, ts.studentcode, ts.schoolid, ts.classcode, tc.grade, tc.class, tc.type, tc.teachercode FROM (SELECT * FROM student_card) ts, (SELECT * FROM t_class) tc WHERE ts.classcode = tc.classcode AND tc.classcode = :classcode  AND tc.schoolcode = :schoolcode  AND tc.type = :schooltype";
		List list = null;
		if(big.getCurrentpage() != null && big.getPagesize() != null){
			 list = getDao().queryBySqlPage(sql,big,big.getCurrentpage(),big.getPagesize()); 
			 String sql2 = "select count(*) from ("+ sql +") t";
			int counts = 0;
			counts = getDao().getCountBySqlPage(sql2,big);
			map.put("total", counts);
		}else {
			 list = getDao().queryBySQL(sql,big);
		}
		List<StudentInfoBean> result = new ArrayList<StudentInfoBean>();
		for(int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			StudentInfoBean student = new StudentInfoBean();
			student.setStudentname(String.valueOf(obj[0]));
			student.setStudentcode(String.valueOf(obj[1]));
			student.setSchoolcode(String.valueOf(obj[2]));
			student.setClasscode(String.valueOf(obj[3]));
			student.setGrades(String.valueOf(obj[4]));
			student.setClasses(String.valueOf(obj[5]));
			student.setSchooltype(String.valueOf(obj[6]));
			student.setTeachercode(String.valueOf(obj[7]));
			result.add(student);
		}
		map.put("data", result);
		return map;
	}
	
	public Map getTeahcerAllStudent(BigExamBean big){
		Map map = new HashMap();
		List list = new ArrayList();
		String sql = " SELECT studentcode,studentname,schoolcode,classcode,grade,classes,TYPE,scorenumber FROM t_result "
				+ " WHERE coursecode = :coursecode and schoolcode =:schoolcode and classcode = :classcode and type = :schooltype and examcode = :examcode";
		
		if(big.getCurrentpage() != null && big.getPagesize() != null){
			 list = getDao().queryBySqlPage(sql,big,big.getCurrentpage(),big.getPagesize()); 
			 String sql2 = "select count(*) from ("+ sql +") t";
			int counts = 0;
			counts = getDao().getCountBySqlPage(sql2,big);
			map.put("total", counts);
		}else {
			 list = getDao().queryBySQL(sql,big);
		}
		List<StudentInfoBean> result = new ArrayList<StudentInfoBean>();
		for(int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			StudentInfoBean student = new StudentInfoBean();
			student.setStudentname(String.valueOf(obj[1]));
			student.setStudentcode(String.valueOf(obj[0]));
			student.setSchoolcode(String.valueOf(obj[2]));
			student.setClasscode(String.valueOf(obj[3]));
			student.setGrades(String.valueOf(obj[4]));
			student.setClasses(String.valueOf(obj[5]));
			student.setSchooltype(String.valueOf(obj[6]));
			student.setScorenumber(String.valueOf(obj[7]));
			result.add(student);
		}
		map.put("data", result);
		return map;
	}

	/**获取教师端小测考试列表
	 * @author:wuqiwei
	 * @param @param small
	 * @param @return
	 * @date:2016年6月27日下午5:09:22
	*/
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Map getTeacherTestList(SmallTestBean small) {
		String sql = "select tt.* from (SELECT te.examcode, te.subjectcode, te.starttime, te.endtime, te.remark, te.usercode, te.usertype, te.schoolcode, te.createtime, te.describes, tx.classcode, tx.studentcode,te.id "
				+ " FROM (SELECT * FROM t_exam) te, "
				+ " (SELECT * FROM t_examinationresult) tx WHERE te.examcode = tx.examcode AND tx.classcode = :classcode AND te.schoolcode = :schoolcode ";
		if(small.getStime() != null){
			sql += "  AND te.createtime >= :stime";
		}
		if(small.getEtime() != null){
			sql += " and te.createtime <= :etime";
		}
			sql += " GROUP BY te.examcode order by  te.createtime desc) tt where  1= 1";
		List list = getDao().queryBySqlPage(sql, small, small.getCurrentpage(), small.getPagesize());
		String sql2 = "select count(*) from ("+ sql +") t";
		int counts = 0;
		counts = getDao().getCountBySqlPage(sql2,small);
		List<SmallTestBean> result = new ArrayList<SmallTestBean>();
		for(int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			SmallTestBean test = new SmallTestBean();
			test.setExamcode(String.valueOf(obj[0]));
			test.setSubjectcode(String.valueOf(obj[1]));
			test.setStime(Long.valueOf(String.valueOf(obj[2])));
			test.setEtime(Long.valueOf(String.valueOf(obj[3])));
			test.setExamname(String.valueOf(obj[4]));
			test.setUsercode(String.valueOf(obj[5]));
			test.setUsertype(String.valueOf(obj[6]));
			test.setSchoolcode(String.valueOf(obj[7]));
			test.setCreatetime(Long.valueOf(String.valueOf(obj[8])));
			test.setDescribes(obj[9] != null ?String.valueOf(obj[9]) :"");
			test.setClasscode(String.valueOf(obj[10]));
			test.setStudentcode(String.valueOf(obj[11]));
			test.setId(Long.valueOf(String.valueOf(obj[12])));
			result.add(test);
		}
		Map map = new HashMap();
		map.put("data", result);
		map.put("total", counts);
		return map;
	}

	/**添加教师端小测考试信息
	 * @author:wuqiwei
	 * @param @param small
	 * @param @return
	 * @date:2016年6月28日上午9:35:03
	*/
	public boolean insertTeacherSmallEaxmInfo(SmallTestBean small) {
		boolean flag = false;
		String sql = "insert into t_exam(examcode,subjectcode,starttime,endtime,resulttime,remark,usercode,usertype,schoolcode,createtime,describes) values('"+small.getCreatetime()+"','"+small.getSubjectcode()+"','"+small.getStime()+"','"+small.getEtime()+"','"+small.getCreatetime()+"','"+small.getExamname()+"','"+small.getUsercode()+"','"+small.getUsertype()+"','"+small.getSchoolcode()+"','"+small.getCreatetime()+"','"+small.getDescribes()+"')";
		int a = getDao().executeSQL(sql);
		if( a > 0){
			//查询这个班级的所有学生
			String qsql = " SELECT ts.studentcode FROM (SELECT * FROM t_class ) tc,(SELECT * FROM t_student ) ts WHERE tc.classcode = ts.classcode AND tc.classcode = :classcode";
			List list = getDao().queryBySQL(qsql, small);
			for(int i = 0;i<list.size();i++){
				String isql = " insert into t_examinationresult(classcode,examcode,studentcode,result) values('"+small.getClasscode()+"','"+small.getCreatetime()+"','"+list.get(i)+"','0')";
				int b = getDao().executeSQL(isql);
				if( b > 0){
					flag = true;
				}
			}
		}
		return flag;
	}

	/**教师端录入成绩
	 * @author:wuqiwei
	 * @param @param small
	 * @param @return
	 * @date:2016年6月28日上午10:59:39
	*/
	public boolean teacherEntryResult(List<SmallTestBean> small) {
		boolean flag = false;
		for (SmallTestBean test : small) {
			String sql = " update t_examinationresult set result = '"+test.getResult()+"' where classcode = '"+test.getClasscode()+"' and examcode = '"+test.getExamcode()+"' and studentcode = '"+test.getStudentcode()+"'";
			int a = getDao().executeSQL(sql);
			if( a > 0){
				flag = true;
			}
		}
		return flag;
	}
	/**
	 * 教师端添加校考成绩
	 * @author:wuqiwei
	 * @param @param small
	 * @param @return
	 * @date:2016年6月29日下午8:35:13
	 */
	public boolean teacherEntryResultInfo(List<SmallTestBean> small) {
		boolean flag = false;
		for (SmallTestBean test : small) {
			String sql = " update t_result set scorenumber = '"+test.getResult()+"' where classcode = '"+test.getClasscode()+"' and examcode = '"+test.getExamcode()+"' and studentcode = '"+test.getStudentcode()+"' and coursecode = '"+test.getCoursecode()+"'";
			int a = getDao().executeSQL(sql);
			if( a > 0){
				flag = true;
			}
		}
		return flag;
	}

	/**获取教师端班级小测学生列表
	 * @author:wuqiwei
	 * @param @param small
	 * @param @return
	 * @date:2016年6月28日上午11:02:45
	*/
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Map queryTeacherStudent(SmallTestBean small) {
		Map map = new HashMap();
		String sql = " SELECT ts.id,ts.name,ts.studentcode,tc.grade,tc.class,te.result  FROM (SELECT * FROM t_examinationresult) te,(SELECT * FROM t_student ) ts,(SELECT * FROM t_class ) tc  WHERE te.classcode = ts.classcode   AND te.studentcode = ts.studentcode AND tc.classcode = ts.classcode  AND  te.classcode = :classcode   AND te.examcode = :examcode GROUP BY  ts.id";
		List list = null;
		if(small.getCurrentpage() != null && small.getPagesize() != null){
			 list = getDao().queryBySqlPage(sql,small,small.getCurrentpage(),small.getPagesize()); 
			 String sql2 = "select count(*) from ("+ sql +") t";
			int counts = 0;
			counts = getDao().getCountBySqlPage(sql2,small);
			map.put("total", counts);
		}else {
			 list = getDao().queryBySQL(sql,small);
		}
		List<StudentInfoBean> result = new ArrayList<StudentInfoBean>();
		for(int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			StudentInfoBean student = new StudentInfoBean();
			student.setId(Long.valueOf(String.valueOf(obj[0])));
			student.setStudentname(String.valueOf(obj[1]));
			student.setStudentcode(String.valueOf(obj[2]));
			student.setGrades(String.valueOf(obj[3]));
			student.setClasses(String.valueOf(obj[4]));
			student.setResult(String.valueOf(obj[5]));
			result.add(student);
		}
		map.put("data", result);
		return map;
	}

	/**修改教师端考试信息
	 * @author:wuqiwei
	 * @param @param small
	 * @param @return
	 * @date:2016年6月28日下午1:30:08
	*/
	public boolean updateTeahcerExamInfo(SmallTestBean small) {
		boolean flag = false;
		String sql = "update t_exam set examcode = '"+small.getExamcode()+"',subjectcode = '"+small.getSubjectcode()+"',starttime = '"+small.getStime()+"',endtime = '"+small.getEtime()+"',resulttime = '"+small.getCreatetime()+"',remark = '"+small.getExamname()+"',usercode = '"+small.getUsercode()+"',usertype = '"+small.getUsertype()+"',schoolcode = '"+small.getSchoolcode()+"',createtime = '"+small.getCreatetime()+"',describes = '"+small.getDescribes()+"' where id = '"+small.getId()+"'";
		int a = getDao().executeSQL(sql);
		if( a > 0){
			flag = true;
		}
		return flag;
	}

	/**删除教师端考试信息
	 * @author:wuqiwei
	 * @param @param small
	 * @param @return
	 * @date:2016年6月28日下午1:57:35
	*/
	public boolean deleteTeacherExamInfo(SmallTestBean small) {
		boolean flag = false;
		String sql = " delete from t_exam  where examcode = :examcode";
		int a = getDao().executeSQL(sql, small);
		if(a > 0){
			String dsql = " delete from t_examinationresult where examcode = :examcode";
			int b = getDao().executeSQL(dsql, small);
			if( b > 0){
				flag = true;
			}
		}
		return flag;
	}

	/**获取教师端成绩列表
	 * @author:wuqiwei
	 * @param @param small
	 * @param @return
	 * @date:2016年6月28日下午2:12:22
	*/
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Map getTeacherResultList(SmallTestBean small) {
		queryStudent(small);
		Map map = new HashMap();
		String sql = " select tt.* from (SELECT  t.classcode,t.examcode,t.studentcode,tc.grade,tc.class,tc.schoolcode,t.result,@rownum \\:= @rownum+1 FROM (SELECT *,@rownum \\:= 0 FROM t_examinationresult te WHERE examcode = :examcode ORDER BY result*1 desc) t,(SELECT * FROM t_class) tc WHERE t.classcode = tc.classcode ) tt where 1 = 1";
		if(small.getStudentcode() != null && small.getStudentcode().length() > 0){
			sql += " and tt.studentcode = :studentcode";
		}
		List<Object[]> list = null;
		if(small.getCurrentpage() != null && small.getPagesize() != null){
			 list = getDao().queryBySqlPage(sql,small,small.getCurrentpage(),small.getPagesize()); 
			 String sql2 = "select count(*) from ("+ sql +") t";
			int counts = 0;
			counts = getDao().getCountBySqlPage(sql2,small);
			map.put("total", counts);
		}else {
			 list = getDao().queryBySQL(sql,small);
		}
		List<SmallTestBean> result = new ArrayList<SmallTestBean>();
		for(int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			SmallTestBean test =  new SmallTestBean();
			test.setClasscode(String.valueOf(obj[0]));
			test.setExamcode(String.valueOf(obj[1]));
			test.setStudentcode(String.valueOf(obj[2]));
			test.setGrades(String.valueOf(obj[3]));
			test.setClasses(String.valueOf(obj[4]));
			test.setSchoolcode(String.valueOf(obj[5]));
			test.setResult(String.valueOf(obj[6]));
			double rank = Double.valueOf(String.valueOf(obj[7]));
			test.setClassranking(String.valueOf(Math.round(rank)));
			String studentname = String.valueOf(studentInfoMap.get(String.valueOf(obj[2])));
			test.setStudentname(studentname);
			result.add(test);
		}
		map.put("data", result);
		return map;
	}
	public void queryStudent(SmallTestBean small){
		String sql = "SELECT studentcode,NAME FROM t_student WHERE classcode = :classcode";
		List list = getDao().queryBySQL(sql, small);
		for(int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			studentInfoMap.put(String.valueOf(obj[0]), String.valueOf(obj[1]));//根据studentcode唯一 存入学生的姓名
		}
	}
}
